create table safari_freeboard(
	no number primary key,
	id varchar2(50) not null,
	title varchar2(50) not null,
	content varchar2(3000) not null,
	hits number default 0,
	time_posted date not null,
	ref number default 0,
	restep number default 0,
	relevel number default 0,
	--멤버 아이디 게시판 id 와 연결
	constraint fk1_safari_free_id foreign key(id) references safari_member(id)

)
	--코멘트(덧글) 게시판과 연결
	constraint fk1_safari_free_comment foreign key(ref) references safari_freeboard_comment(ref)
-- comment column 쓰면 부적합한 식별자 라고 뜸... 예약어 인가봄.... content로 수정함
create table safari_freeboard_comment(
	no number primary key,
	id varchar2(50) not null,
	content varchar2(3000) not null,
	time_posted date not null,
	ref number default 0,
	restep number default 0
)
	
drop table safari_freeboard_comment


drop table safari_freeboard
create sequence free_seq nocache;

insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('1234','1234','한명기','용인',19900908,'male','01049381326','to1249@nate.com','customer',5000);

-- freeboard 게시판 글 등록
insert into safari_freeboard(no,id,title,content,time_posted) values(free_seq.nextval,'1234','첫번쨰글','첫번쨰글내용',sysdate)
select count(*) from safari_freeboard
-- freeboard 게시판 글 업데이트
update safari_freeboard set title='첫번쨰글 타이틀 수정',  content='첫번째글 내용 수정' where no=1
-- freeboard 게시판 글 삭제
delete safari_freeboard where no=#{no}

-- 게시판 글 상세정보 보기
select id,

-- 게시판 글 페이지 그룹별 보기
		select no,id, title,
		content, hits,time_posted, ref, restep, relevel from (
		select no,id, title,
		content, hits,time_posted, ref, restep, relevel,
		ceil(rownum/10) as page from (
		select no,id, title,
		content, hits,to_char(time_posted,'YYYY.MM.DD')as time_posted, ref, restep, relevel from safari_freeboard
		order by ref desc, restep asc
		)
		)where page=#{value}
		
-- 게시물 글 몇개인지 보기
		select count(no) from safari_freeboard
		select count(*) from safari_freeboard
		
		select * from safari_freeboard